package com.xquant.marketdata.utils;

import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelInsertInfo {

    //"D:\\Users\\xpp\\Downloads\\1.xlsx";
    /*
     * 文件路径
     * */
    private String excelPath;
    //1开始于哪一行
    private int insertStartPointer;
    //在当前工作薄的那个工作表单中插入这行数据 eg: Sheet1
    private String sheetName;

    public String getExcelPath() {
        return excelPath;
    }

    public void setExcelPath(String excelPath) {
        this.excelPath = excelPath;
    }

    public int getInsertStartPointer() {
        return insertStartPointer;
    }

    public void setInsertStartPointer(int insertStartPointer) {
        this.insertStartPointer = insertStartPointer;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    /**
     * @param excelPath          文件路径
     * @param insertStartPointer 插入行开始
     * @param sheetName          插入的工作表名称
     */
    public ExcelInsertInfo(String excelPath, int insertStartPointer, String sheetName) {
        this.excelPath = excelPath;
        this.insertStartPointer = insertStartPointer;
        this.sheetName = sheetName;
    }


    /**
     * 总的入口方法
     */
    public static void main(String[] args) throws UnsupportedEncodingException {
        ExcelInsertInfo crt = new ExcelInsertInfo("D:\\myexcel3.xlsx", 0, "hidden");
        ArrayList<Map> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            HashMap<Object, Object> hashMap = new HashMap<>();
            hashMap.put(i + i + "00", i * i + "");
            hashMap.put(i + i + "0", i * i + "");
            hashMap.put(i + i + "1", i * i + "");
            list.add(hashMap);
        }
        //crt.insertMapRows("hidden",list);
        /*String s = "http://localhost:8888/otcOption/downLoadExcel.action?fileName=EXCEL%25E6%258F%2592%25E4%25BB%25B6_office2007.xlsx&defaultExcel=true";
        String s1 =  URLDecoder.decode(s,"utf-8");
        System.out.println(ServerConfig.getUrl());*/
    }

    /**
     * 在已有的Excel文件中插入一行新的数据的入口方法
     */
    public void insertRows(String sheetName, List<String> list) {
        XSSFWorkbook wb = returnWorkBookGivenFileHandle();
        XSSFSheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            sheet = wb.createSheet(sheetName);
        } else {
            int sheetIndex = wb.getSheetIndex(sheetName);
            wb.removeSheetAt(sheetIndex);
            sheet = wb.createSheet(sheetName);
        }
        int sheetIndex = wb.getSheetIndex(sheetName);
        wb.setSheetHidden(sheetIndex, true);
        XSSFRow row = createRow(sheet, insertStartPointer);
        /**
         * 创建要出入的行中单元格
         */
        for (int i = 0; i < list.size(); i++) {
            row.createCell(i).setCellValue(list.get(i));
        }

        //saveExcel(wb);
    }

    /**
     * 在已有的Excel文件中插入一行新的数据的入口方法
     */
    public void insertMapRows(String sheetName, List<List<Map<String, Object>>> lists) {
        SXSSFWorkbook wb = new SXSSFWorkbook(returnWorkBookGivenFileHandle());
        SXSSFSheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            sheet = wb.createSheet(sheetName);
        } else {
            int sheetIndex = wb.getSheetIndex(sheetName);
            wb.removeSheetAt(sheetIndex);
            sheet = wb.createSheet(sheetName);
        }
        sheet.setRandomAccessWindowSize(-1);
        int sheetIndex = wb.getSheetIndex(sheetName);
        wb.setSheetHidden(sheetIndex, true);
        //XSSFRow row = createRow(sheet, insertStartPointer);
        /**
         * 创建要出入的行中单元格
         */
        int t = 0;
        int tempt = 0;
        for (List<Map<String, Object>> list : lists) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> objectMap = list.get(i);
                tempt = objectMap.keySet().size();
                SXSSFRow row = sheet.getRow(i) == null ? sheet.createRow(i) : sheet.getRow(i);
                int tempIndex = t;
                for (String key : objectMap.keySet()) {
                    SXSSFCell cell = row.createCell(tempIndex);
                    cell.setCellValue(objectMap.get(key).toString());
                    tempIndex++;
                }
            }
            t = t + tempt;
        }
        saveExcel(wb);


    }

    /**
     * 保存工作薄
     *
     * @param wb
     */
    private void saveExcel(SXSSFWorkbook wb) {
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(excelPath);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 得到一个已有的工作薄的POI对象
     *
     * @return
     */
    private XSSFWorkbook returnWorkBookGivenFileHandle() {
        XSSFWorkbook wb = null;
        FileInputStream fis = null;
        File f = new File(excelPath);
        try {
            if (f != null) {
                fis = new FileInputStream(f);
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }

    /**
     * 找到需要插入的行数，并新建一个POI的row对象
     *
     * @param sheet
     * @param rowIndex
     * @return
     */
    private XSSFRow createRow(XSSFSheet sheet, Integer rowIndex) {
        XSSFRow row = null;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        return row;
    }
}
